SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author:		@in_Author,,Name>
-- Create date: @in_Create Date,
-- Description:	@in_Description,
-- =============================================
CREATE PROCEDURE [dbo].[usp_Mst_Products_insert]
(
 @in_CategoryId int
,@in_ProductCode varchar(50)
,@in_ProductName varchar(50)
,@in_ProductDesc varchar(250)
,@in_PetaluId int
,@in_Quantity int
,@in_GrossWeight decimal(18,3)
,@in_Wastage decimal(18,3)
,@in_NetWeight decimal(18,3)
,@in_Size decimal(18,2)
,@in_SizeUnitId int
,@in_MakingCharge decimal(18,3)
,@in_StonesDesc varchar(250)
,@in_StonesCount int
,@in_StonesWeight decimal(18,3)
,@in_StonesUnitId int
,@in_TotalStonesCost decimal(18,3)
,@in_StatusId int
,@in_OtherCharges decimal(18, 3)
,@out_ProductId bigint out
,@out_ErrorMsg varchar(250) out
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
BEGIN TRY	
	CREATE TABLE #Temp_Mst_Products(ProductId BIGINT) 
	
		
INSERT INTO [Mst_Products]
           ([CategoryId]
           ,[ProductCode]
           ,[ProductName]
           ,[ProductDesc]
           ,[PetaluId]
           ,[Quantity]
           ,[GrossWeight]
           ,[Wastage]
           ,[NetWeight]
           ,[Size]
           ,[SizeUnitId]
           ,[MakingCharge]
           ,[OtherCharges]
           ,[StonesDesc]
           ,[StonesCount]
           ,[StonesWeight]
           ,[StonesUnitId]
           ,[TotalStonesCost]
           ,[StatusId]) OUTPUT inserted.ProductId into #Temp_Mst_Products(ProductId)       
     VALUES
           (@in_CategoryId 
           ,@in_ProductCode
           ,@in_ProductName 
           ,@in_ProductDesc
           ,@in_PetaluId 
           ,@in_Quantity 
           ,@in_GrossWeight 
           ,@in_Wastage 
           ,@in_NetWeight 
           ,@in_Size 
           ,@in_SizeUnitId 
           ,@in_MakingCharge 
           ,@in_OtherCharges
           ,@in_StonesDesc
           ,@in_StonesCount 
           ,@in_StonesWeight 
           ,@in_StonesUnitId
           ,@in_TotalStonesCost 
           ,@in_StatusId)
           
          SET @out_ProductId=(SELECT ProductId from #Temp_Mst_Products)
               
       END TRY
BEGIN CATCH   
       SET @out_ErrorMsg= ERROR_MESSAGE() 
  END CATCH
END

GO
